<?php

/**
 * 媒资信息综合业务
 * 注意联合查询的时候媒资内容表别名为asset
 */
include_once dirname(dirname(__FILE__)) . DIRECTORY_SEPARATOR . 'nl_common.func.php';
include_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'asset_item.class.php';
include_once dirname(dirname(__FILE__)) . DIRECTORY_SEPARATOR . "label" . DIRECTORY_SEPARATOR . 'video_label_bind_content.class.php';

class nl_asset_item_bo {

        static public $order = array(
            NL_ORDER_NUM_ASC => " order by asset.nns_order asc",
            NL_ORDER_NUM_DESC => " order by asset.nns_order desc",
            NL_ORDER_TIME_ASC => " order by asset.nns_create_time asc",
            NL_ORDER_TIME_DESC => " order by asset.nns_create_time desc",
            NL_ORDER_CLICK_ASC => " order by asset.nns_play_count asc",
            NL_ORDER_CLICK_DESC => " order by asset.nns_play_count desc",
        );
        //缓存时间
        static private $cache_time = 3600;

        /**
         * 从DC模块获取标签列表
         * @param dc DC模块
         * @param string  tag epg输出标识
         * @param  “asset_id”=>媒资包ID
         * @param “category_id”=>媒资包栏目ID

         * @param array 标签组参数
          Array(
          “0”=>标签ID
          “1”=>标签ID
          “2”=>标签ID
          “3”=>标签ID
          )
         * @param int 起始查询记录位置
         * @param int 查询记录条数

         * @param “include_child_category”=>是否包含媒资包子栏目 0为不包含 1为包含
         * @param string 排序规则，NL_ORDER_NUM_ASC | NL_ORDER_NUM_DESC | NL_ORDER_TIME_ASC | NL_ORDER_TIME_DESC | NL_ORDER_CLICK_ASC | NL_ORDER_CLICK_DESC
          默认为 NL_ORDER_NUM_DESC
          NL_ORDER_NUM_ASC 按ORDER字段升序排列
          NL_ORDER_NUM_DESC 按ORDER字段降序排列
          NL_ORDER_TIME_ASC 按创建时间升序排列
          NL_ORDER_TIME_DESC 按创建时间降序排列
          NL_ORDER_CLICK_ASC按点击数升序排列
          NL_ORDER_CLICK_DESC按点击数降序排列

         * @param String 读取策略 NL_DC_AUTO| NL_DC_DB | NL_DC_CACHE
          默认为NL_DC_AUTO
         * @return *
         * 			Array内容列表结果

         * 			FALSE 查询失败
         * 			TRUE  查询成功，但无数据
         */
        static public function epg_get_asset_item_by_labels_category($dc, $tag, $asset_id, $category_id, $labels, $since = 0, $num = 10, $include_child_category=0, $order = NL_ORDER_NUM_ASC, $policy = NL_DC_AUTO, $except_category = null) {
                if ($dc->is_cache_enabled()) {
                        switch ($policy) {
                                case NL_DC_AUTO: $result = self::epg_get_asset_item_by_labels_category_cache($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category);
                                        break;
                                case NL_DC_DB: $result = self::epg_get_asset_item_by_labels_category_db($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category);
                                        break;
                                case NL_DC_CACHE: $result = self::epg_get_asset_item_by_labels_category_cache($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category);
                                        break;
                        }
                } else {
                        $result = self::epg_get_asset_item_by_labels_category_db($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category);
                }
                return $result;
        }

        static private function epg_get_asset_item_by_labels_category_db($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy = NL_DC_AUTO, $except_category = null) {
                //组装标签查询条件
                if (is_array($labels)) {
                        $where_lab = implode("','", $labels);
                        $where_lab = "'" . $where_lab . "'";
                } else {
                        $where_lab = "";
                }
                //通过labels找到labels所属的标签类型id，得出的数据结构为二维数组，以为的键名为类型的id
                $sql = "select nns_id,nns_type from nns_video_label where nns_id in({$where_lab})";

                $data = nl_query_by_db($sql, $dc->db());

                if (is_bool($data)) {
                        return $data;
                }
                foreach ($data as $val) {
                        $label_type_id[$val['nns_type']][] = $val["nns_id"];
                }

                $join_tables = "";
                $joins_filds = "";
                $joins_in = "";
                foreach ($label_type_id as $type_id => $label_ids) {
                        $join_tables.=",nns_video_label_bind_{$type_id}";
                        $joins_filds.=" and asset.nns_video_id=nns_video_label_bind_{$type_id}.nns_video_id ";
                        if (count($label_ids) > 1) {
                                if (!empty($labels)) {
                                        $labs = implode("','", $labels);
                                        $labs = "'" . $labs . "'";
                                        $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ({$labs})";
                                }
                        } else {
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ('{$label_ids[0]}')";
                        }
                }

                if ($include_child_category && $except_category) {
                        if ($except_category) {
                                $except_sql = ' and not ( ';
                                $except_arr = explode('||', $except_category);
                                foreach ($except_arr as $except_item) {
                                        $except_item = explode('|', $except_item);
                                        $except_sql.= " asset.nns_category_id like '" . $except_item[1] . "%' or";
                                }
                                $except_sql = rtrim($except_sql, 'or');
                                $except_sql.=') ';
                        }
                }

                //0不包含子栏目1包含子栏目
                if ($include_child_category == 0) {
                        $child_category = "asset.nns_category_id='{$category_id}'";
                } else {
                        $child_category = "asset.nns_category_id like '{$category_id}%'";
                }

                //组装LIMIT
                if (empty($since) && $since != 0) {
                        $limit = "";
                } elseif (empty($num)) {
                        $limit = "LIMIT $since";
                } else {
                        $limit = "LIMIT {$since},{$num}";
                }

                $order = self::$order[$order];
                //关联标签绑定表和媒资包item表查询
                $sql = "select group_concat(asset.nns_video_id),asset.* from nns_assists_item as asset {$join_tables} where asset.nns_assist_id='{$asset_id}' and {$child_category} and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))  and asset.nns_check='1' {$except_sql} {$joins_filds} {$joins_in} group by asset.nns_video_id {$order} {$limit}";

                $res_data = nl_query_by_db($sql, $dc->db());

                if (is_bool($res_data)) {
                        return $res_data;
                }
                if ($dc->is_cache_enabled()) {
                        $cache = $dc->cache();
                        //获取媒资包key
                        $asset_key = $cache->get("index|#" . $asset_id);
                        //获取标签key
                        $label_key = $cache->get("label");
                        //如果2个key都获取成功则设置缓存
                        if (!empty($asset_key) && empty($label_key)) {
                                //组装自己的key
                                $me_key = $tag . $asset_id . $category_id . $where_lab . $since . $num . $include_child_category . self::$order[$order];
                                $cache->set($asset_key . $label_key . md5($me_key), $res_data, self::$cache_time);
                        }
                }
                return $res_data;
        }

        static private function epg_get_asset_item_by_labels_category_cache($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category = null) {
                //组装标签查询条件
                if (is_array($labels)) {
                        $where_lab = implode("','", $labels);
                        $where_lab = "'" . $where_lab . "'";
                } else {
                        $where_lab = "";
                }
                $cache = $dc->cache();
                //获取媒资包key
                $asset_key = $cache->get("index|#" . $asset_id);
                //获取标签key
                $label_key = $cache->get("label");
                //只有有一个获取失败
                if (empty($asset_key) || empty($label_key)) {
                        //判断是否需要从数据库读取
                        if ($policy == NL_DC_AUTO) {
                                $result = self::epg_get_asset_item_by_labels_category_db($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category);
                                return $result;
                        } else {
                                return true;
                        }
                }
                //组装自己的key
                $me_key = $tag . $asset_id . $category_id . $where_lab . $since . $num . $include_child_category . self::$order[$order] . $except_category;
                //如果获取成功则组装缓存的结果集key
                $key = $asset_key . $label_key . md5($me_key);
                $res_data = $cache->get($key);
                //如果获取失败
                if (empty($res_data)) {
                        //判断是否需要从数据库读取
                        if ($policy == NL_DC_AUTO) {
                                $result = self::epg_get_asset_item_by_labels_category_db($dc, $tag, $asset_id, $category_id, $labels, $since, $num, $include_child_category, $order, $policy, $except_category);
                                return $result;
                        } else {
                                return true;
                        }
                }
                return $res_data;
        }

        /**
         * 从DC模块获取标签列表
         * @param dc DC模块
         * @param string  tag  epg输出标识
         * @param array 标签组参数
          Array(
          “0”=>标签ID
          “1”=>标签ID
          “2”=>标签ID
          “3”=>标签ID
          )
         * @param int 起始查询记录位置
         * @param int 查询记录条数
         * @param string 排序规则，NL_ORDER_NUM_ASC | NL_ORDER_NUM_DESC | NL_ORDER_TIME_ASC | NL_ORDER_TIME_DESC | NL_ORDER_CLICK_ASC | NL_ORDER_CLICK_DESC
          默认为 NL_ORDER_NUM_DESC
          NL_ORDER_NUM_ASC 按ORDER字段升序排列
          NL_ORDER_NUM_DESC 按ORDER字段降序排列
          NL_ORDER_TIME_ASC 按创建时间升序排列
          NL_ORDER_TIME_ DESC 按创建时间降序排列
          NL_ORDER_CLICK_ASC按点击数升序排列
          NL_ORDER_CLICK_DESC按点击数降序排列

         * @param String 读取策略 NL_DC_AUTO| NL_DC_DB | NL_DC_CACHE
          默认为NL_DC_AUTO
         * @return *
         * 			Array内容列表结果
         * 			FALSE 查询失败
         * 			TRUE  查询成功，但无数据
         */
        static public function epg_get_asset_item_by_labels($dc, $tag, $labels, $since, $num, $order = NL_ORDER_NUM_ASC, $policy = NL_DC_AUTO) {
                if (!is_array($labels)) {
                        return true;
                }
                /*                 * 思路：
                 * 1、计算出所有的标签类型，就知道需要关联那些标签绑定表
                 * 2、从所有的绑定表中查出所有的影片资源id
                 * 3、根据影片资源id从媒资包查询需要的影片信息
                 */
                if ($dc->is_cache_enabled()) {
                        switch ($policy) {
                                case NL_DC_AUTO: $result = self::epg_get_asset_item_by_labels_cache($dc, $tag, $labels, $since, $num, $order);
                                        break;
                                case NL_DC_DB: $result = self::epg_get_asset_item_by_labels_db($dc, $tag, $labels, $since, $num, $order);
                                        break;
                                case NL_DC_CACHE: $result = self::epg_get_asset_item_by_labels_cache($dc, $tag, $labels, $since, $num, $order);
                                        break;
                        }
                } else {
                        $result = self::epg_get_asset_item_by_labels_db($dc, $tag, $labels, $since, $num, $order);
                }
                return $result;
        }

        /**
         * 从数据库获取指定标签关联的影片信息
         * @param DC $dc
         * @param string $tag
         * @param array $labels
         * @param int $since
         * @param int $num
         * @param string $order
         * @return 结果集为空返回true，查询失败返回false，查询成功返回二维数组结果集，包含所有的影片信息
         */
        static public function epg_get_asset_item_by_labels_cache($dc, $tag, $labels, $since, $num, $order) {
                //连接labels
                $labels_join = implode("','", $labels);
                $labels_join = "'" . $labels_join . "'";
                $cache = $dc->cache();
                //获取标签key
                $label_key = $cache->get("label");
                //组装查询key
                $cache_key = $label_key . '|#' . $tag . $labels_join . $since . $num;
                $cache_data = $cache->get($cache_key);
                if (is_bool($label_key) || is_bool($cache_data)) {
                        $cache_data = self::epg_get_asset_item_by_labels_db($dc, $tag, $labels, $since, $num, $order);
                        $cache->set($cache_key, $cache_data, self::$cache_time);
                }
                return $cache_data;
        }

        /**
         * 从数据库获取指定标签关联的影片信息
         * @param DC $dc
         * @param string $tag
         * @param array $labels
         * @param int $since
         * @param int $num
         * @param string $order
         * @return 结果集为空返回true，查询失败返回false，查询成功返回二维数组结果集，包含所有的影片信息
         */
        static public function epg_get_asset_item_by_labels_db($dc, $tag, $labels, $since, $num, $order) {
                //计算出所有的标签类型
                $type_array = array(); //将所有的标签类型id存放入一维数组的key中，标签id存入二维数组的value中
                foreach ($labels as $label) {
                        $type = substr($label, 0, 4);
                        $type_array[$type][] = $label;
                }
                //一种类型标签一种类型标签的查询出影片id
                $video_ids = array(); //一维索引数组
                foreach ($type_array as $type => $type_labels) {
                        //连接labels
                        $type_label = null;
                        $type_label = implode("','", $type_labels);
                        $type_label = "'" . $type_label . "'";
                        $bind_table = "nns_video_label_bind_{$type}";
                        $get_video_id_sql = "select {$bind_table}.nns_video_id as id from {$bind_table} where nns_label_id in({$type_label})";
                        $res = nl_query_by_db($get_video_id_sql, $dc->db());
                        if (!is_bool($res)) {
                                foreach ($res as $val) {
                                        $video_ids[] = $val["id"];
                                }
                        }
                }
                //到此所有的影片id已经存入$video_ids
                $video_id = implode("','", $video_ids);
                $video_id = "'" . $video_id . "'";
                //组装LIMIT
                if (empty($since) && $since != 0) {
                        $limit = "";
                } elseif (empty($num)) {
                        $limit = "LIMIE $since";
                } else {
                        $limit = "LIMIT {$since},{$num}";
                }
                $order = self::$order[$order];
                //到媒资包查询影片信息
                $sql = "select asset.* from nns_assists_item as asset where asset.nns_video_id in ({$video_id})  and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag)) and asset.nns_check='1' {$order} {$limit}";
                $re = nl_query_by_db($sql, $dc->db());
                return $re;
        }

        /**
         * 从DC模块获取标签列表
         * @param dc DC模块
         * @param string  tag epg输出标识
         * @param  “asset_id”=>媒资包ID
         * @param “category_id”=>媒资包栏目ID

         * @param array 标签组参数
          Array(
          “0”=>标签ID
          “1”=>标签ID
          “2”=>标签ID
          “3”=>标签ID
          )
         * @param “include_child_category”=>是否包含媒资包子栏目 0为不包含 1为包含
         * @return *
         * 		    int 内容列表结果,没有数据返回0
         * 			FALSE 查询失败
         */
        static public function count_asset_by_labels($dc, $tag, $asset_id, $category_id, $labels, $include_child_category = 0, $except_category = null) {
                //组装标签查询条件
//		if(is_array($labels)){
//			$where_lab=implode("','", $labels);
//			$where_lab="'".$where_lab."'";
//		}else{
//			$where_lab="";
//		}
//		//0不包含子栏目1包含子栏目
//		if($include_child_category==0){
//			$child_category="asset.nns_category_id='{$category_id}'";
//		}else{
//			$child_category="asset.nns_category_id like '{$category_id}%'";
//		}
                $category_id = $category_id ? $category_id : '1000';
                if (is_array($labels)) {
                        $where_lab = implode("','", $labels);
                        $where_lab = "'" . $where_lab . "'";
                } else {
                        $where_lab = "";
                }
                //通过labels找到labels所属的标签类型id，得出的数据结构为二维数组，以为的键名为类型的id
                $sql = "select nns_id,nns_type from nns_video_label where nns_id in({$where_lab})";
                $data = nl_query_by_db($sql, $dc->db());
                if (is_bool($data)) {
                        return 0;
                }
                foreach ($data as $val) {
                        $label_type_id[$val['nns_type']][] = $val["nns_id"];
                }
                $join_tables = "";
                $joins_filds = "";
                $joins_in = "";
                foreach ($label_type_id as $type_id => $label_ids) {
                        $join_tables.=",nns_video_label_bind_{$type_id}";
                        $joins_filds.=" and asset.nns_video_id=nns_video_label_bind_{$type_id}.nns_video_id ";
                        if (count($label_ids) > 1) {
                                $labs = implode("','", $labels);
                                $labs = "'" . $labs . "'";
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ({$labs})";
                        } else {
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ('{$label_ids[0]}')";
                        }
                }

                if ($include_child_category && $except_category) {
                        if ($except_category) {
                                $except_sql = ' and not ( ';
                                $except_arr = explode('||', $except_category);
                                foreach ($except_arr as $except_item) {
                                        $except_item = explode('|', $except_item);
                                        $except_sql.= " asset.nns_category_id like '" . $except_item[1] . "%' or";
                                }
                                $except_sql = rtrim($except_sql, 'or');
                                $except_sql.=') ';
                        }
                }


                //0不包含子栏目1包含子栏目
                if ($include_child_category == 0) {
                        $child_category = "asset.nns_category_id='{$category_id}'";
                } else {
                        $child_category = "asset.nns_category_id like '{$category_id}%'";
                }
                //$sql="select count(asset.nns_id) as c from nns_assists_item as asset,nns_video_label_bind_content as bind where asset.nns_video_id=bind.nns_video_id  and asset.nns_assist_id='{$asset_id}' and {$child_category} and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag)) and bind.nns_label_id in ({$where_lab})";
                $sql = "select 1 as num from nns_assists_item as asset {$join_tables} where asset.nns_assist_id='{$asset_id}' and {$child_category} and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag)) and asset.nns_check='1' {$except_sql} {$joins_filds} {$joins_in} ";

                $sql.="group by asset.nns_video_id ";

                $r = nl_query_by_db($sql, $dc->db());

                if (is_bool($r)) {
                        return 0;
                }
                return count($r);
        }

        /**
         * 按照拼音类型搜索
         * @param $dc
         * @param string $tag:epg输出标示
         * @param String 媒资包ID
         * @param String 栏目ID
         * @param array 标签组
         * @param int $since:起始页
         * @param int $num:每页大小
         * @param bool $if_include_child:是否包含子栏目0不包含，1包含，默认包含
         * @param string $type
         * 可以的取值为：name_firstchar 名称首字查询
          name_likechar名称模糊查询
          pinyin_firstchar 首字母查询
          en_firstchar 英文首字母查询
          @param string $search:查询字符串
          @return bool:参数不对或者查询出错返回false，查询结果为空返回true
          @return array $res:查询结果集
         */
        static public function epg_get_asset_item_search_by_labels($dc, $tag, $asset_id, $category_id, $labels, $since = 0, $num = 10, $order = NL_ORDER_NUM_ASC, $include_child_category = 1, $type = "pinyin_firstchar", $search = null, $video_type = null, $except_category = null) {
                //组装标签查询条件
//		if(is_array($labels)){
//			$where_lab=implode("','", $labels);
//			$where_lab="'".$where_lab."'";
//		}else{
//			$where_lab="";
//		}
//    	//0不包含子栏目1包含子栏目
//		if($if_include_child==0){
//			$child_category="asset.nns_category_id='{$category_id}'";
//		}else{
//			$child_category="asset.nns_category_id like '{$category_id}%'";
//		}
//		//组装LIMIT
//		if(empty($since)&&$since!=0){
//			$limit="";
//		}elseif(empty($num)){
//			$limit="LIMIE $since";
//		}else{
//			$limit="LIMIT {$since},{$num}";
//		}
//组装标签查询条件
                if (is_array($labels)) {
                        $where_lab = implode("','", $labels);
                        $where_lab = "'" . $where_lab . "'";
                } else {
                        $where_lab = "";
                }
                //通过labels找到labels所属的标签类型id，得出的数据结构为二维数组，以为的键名为类型的id
                $sql = "select nns_id,nns_type from nns_video_label where nns_id in({$where_lab})";
                $data = nl_query_by_db($sql, $dc->db());
                if (is_bool($data)) {
                        return $data;
                }
                foreach ($data as $val) {
                        $label_type_id[$val['nns_type']][] = $val["nns_id"];
                }
                $join_tables = "";
                $joins_filds = "";
                $joins_in = "";
                foreach ($label_type_id as $type_id => $label_ids) {
                        $join_tables.=",nns_video_label_bind_{$type_id}";
                        $joins_filds.=" and asset.nns_video_id=nns_video_label_bind_{$type_id}.nns_video_id ";
                        if (count($label_ids) > 1) {
                                $labs = implode("','", $labels);
                                $labs = "'" . $labs . "'";
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ({$labs})";
                        } else {
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ('{$label_ids[0]}')";
                        }
                }

                if ($include_child_category && $except_category) {
                        $except_sql = ' and not ( ';
                        $except_arr = explode('||', $except_category);
                        foreach ($except_arr as $except_item) {
                                $except_item = explode('|', $except_item);
                                $except_sql.= " (asset.nns_category_id like '" . $except_item[1] . "%' and ";
                                $except_sql.=" asset.nns_assist_id='" . $except_item[0] . "') or";
                        }
                        $except_sql = rtrim($except_sql, 'or');
                        $except_sql.=') ';
                }


//		当媒资包ID未传时,应该为查询所有媒资 BY S67 2013-03-22
                if (!empty($asset_id)) {
                        $asset_sql = " asset.nns_assist_id='{$asset_id}' ";
                        //0不包含子栏目1包含子栏目
                        if ($include_child_category == 0) {
                                $child_category = "and asset.nns_category_id='{$category_id}'";
                        } else {
                                $child_category = "and asset.nns_category_id like '{$category_id}%'";
                        }
                } else {
                        $asset_sql = ' 1=1 ';
                        $child_category = '';
                }
                //组装LIMIT
                if (empty($since) && $since != 0) {
                        $limit = "";
                } elseif (empty($num)) {
                        $limit = "LIMIE $since";
                } else {
                        $limit = "LIMIT {$since},{$num}";
                }
                $where_like = '';
                if (!empty($search)) {
                        $types = explode('|', $type);
                        $where_like = " and (";
                        foreach ($types as $type_value) {
                                switch ($type_value) {
                                        case "name_firstchar": $where_like.="  asset.nns_video_name like '{$search}%'";
                                                break;
                                        case "name_likechar": $where_like.="  asset.nns_video_name like '%{$search}%'";
                                                break;
                                        //    		case "pinyin_firstchar": $where_like.="  asset.nns_pinyin like '{$search}%'";break;
                                        case "pinyin_likechar": $where_like.="  asset.nns_pinyin like '%{$search}%'";
                                                break;
                                        case "en_firstchar": $where_like.="  asset.nns_english_index like '{$search}%'";
                                                break;
                                        case "en_likechar": $where_like.="  asset.nns_english_index like '%{$search}%'";
                                                break;
                                        default: $where_like.=" asset.nns_pinyin like '{$search}%'";
                                                break;
                                }

                                $where_like .=' or';
                        }
                        $where_like = rtrim($where_like, 'or');
                        $where_like.=") ";
                }
                if ($tag !== NULL && $tag !== '') {
                        $where_tag = "and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))";
                }

                $where_type = '';
                if ($video_type !== NULL && $video_type !== '') {
                        $where_type = "and asset.nns_video_type='{$video_type}' ";
                }

                $group = 'group by asset.nns_video_id';

                $order = self::$order[$order];
                $sql = "select asset.* from nns_assists_item as asset {$join_tables} where {$asset_sql} {$child_category} {$where_tag} {$where_type}  {$joins_filds} {$joins_in} {$where_like} {$except_sql} {$group} {$order} {$limit}";
                //$sql="select count(asset.*) as sum from nns_assists_item as asset,nns_video_label_bind_content as bind where asset.nns_video_id=bind.nns_video_id  and asset.nns_assist_id='{$asset_id}' and {$child_category} and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))  and {$where_like} and bind.nns_label_id in ({$where_lab})";
                return nl_query_by_db($sql, $dc->db());
        }

        /**
         * 按照拼音类型搜索
         * @param $dc
         * @param string $tag:epg输出标示
         * @param String 媒资包ID
         * @param String 栏目ID
         * @param array 标签组
         * @param bool $if_include_child:是否包含子栏目0不包含，1包含，默认包含
         * @param string $type
         * 可以的取值为：name_firstchar 名称首字查询
          name_likechar名称模糊查询
          pinyin_firstchar 首字母查询
          en_firstchar 英文首字母查询
          @param string $search:搜索内容
          @return bool:参数不对或者查询出错返回false，查询结果为空返回true
          @return array $res:查询结果集
         */
        static public function count_asset_item_search_by_labels($dc, $tag, $asset_id, $category_id, $labels, $include_child_category = 1, $type = "pinyin_firstchar", $search = null, $video_type = null, $except_category = null) {
                //组装标签查询条件
//		if(is_array($labels)){
//			$where_lab=implode("','", $labels);
//			$where_lab="'".$where_lab."'";
//		}else{
//			$where_lab="";
//		}
//    	//0不包含子栏目1包含子栏目
//		if($if_include_child==0){
//			$child_category="asset.nns_category_id='{$category_id}'";
//		}else{
//			$child_category="asset.nns_category_id like '{$category_id}%'";
//		}
//    	switch ($type){
//    		case "name_firstchar": $where_like="nns_video_name like '{$search}%'";
//    		case "name_likechar": $where_like="nns_video_name like '%{$search}%'";
//    		case "pinyin_firstchar": $where_like="nns_pinyin like '{$search}%'";
//    		default: $where_like="nns_english_index like '{$search}%'";
//    	}

                if (is_array($labels)) {
                        $where_lab = implode("','", $labels);
                        $where_lab = "'" . $where_lab . "'";
                } else {
                        $where_lab = "";
                }
                //通过labels找到labels所属的标签类型id，得出的数据结构为二维数组，以为的键名为类型的id
                $sql = "select nns_id,nns_type from nns_video_label where nns_id in({$where_lab})";
                $data = nl_query_by_db($sql, $dc->db());
                if (is_bool($data)) {
                        return $data;
                }
                foreach ($data as $val) {
                        $label_type_id[$val['nns_type']][] = $val["nns_id"];
                }
                $join_tables = "";
                $joins_filds = "";
                $joins_in = "";
                foreach ($label_type_id as $type_id => $label_ids) {
                        $join_tables.=",nns_video_label_bind_{$type_id}";
                        $joins_filds.=" and asset.nns_video_id=nns_video_label_bind_{$type_id}.nns_video_id ";
                        if (count($label_ids) > 1) {
                                $labs = implode("','", $labels);
                                $labs = "'" . $labs . "'";
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ({$labs})";
                        } else {
                                $joins_in.=" and nns_video_label_bind_{$type_id}.nns_label_id in ('{$label_ids[0]}')";
                        }
                }


                if ($include_child_category && $except_category) {
                        $except_sql = ' and not ( ';
                        $except_arr = explode('||', $except_category);
                        foreach ($except_arr as $except_item) {
                                $except_item = explode('|', $except_item);
                                $except_sql.= " (asset.nns_category_id like '" . $except_item[1] . "%' and ";
                                $except_sql.=" asset.nns_assist_id='" . $except_item[0] . "') or";
                        }
                        $except_sql = rtrim($except_sql, 'or');
                        $except_sql.=') ';
                }



//		当媒资包ID未传时,应该为查询所有媒资 BY S67 2013-03-22
                if (!empty($asset_id)) {
                        $asset_sql = " asset.nns_assist_id='{$asset_id}' ";
                        //0不包含子栏目1包含子栏目
                        if ($include_child_category == 0) {
                                $child_category = "and asset.nns_category_id='{$category_id}'";
                        } else {
                                $child_category = "and asset.nns_category_id like '{$category_id}%'";
                        }
                } else {
                        $asset_sql = ' 1=1 ';
                        $child_category = '';
                }
                $where_like = '';
                if (!empty($search)) {
                        $types = explode('|', $type);
                        $where_like = " and (";
                        foreach ($types as $type_value) {
                                switch ($type_value) {
                                        case "name_firstchar": $where_like.="  asset.nns_video_name like '{$search}%'";
                                                break;
                                        case "name_likechar": $where_like.="  asset.nns_video_name like '%{$search}%'";
                                                break;
                                        //    		case "pinyin_firstchar": $where_like.="  asset.nns_pinyin like '{$search}%'";break;
                                        case "pinyin_likechar": $where_like.="  asset.nns_pinyin like '%{$search}%'";
                                                break;
                                        case "en_firstchar": $where_like.="  asset.nns_english_index like '{$search}%'";
                                                break;
                                        case "en_likechar": $where_like.="  asset.nns_english_index like '%{$search}%'";
                                                break;
                                        default: $where_like.=" asset.nns_pinyin like '{$search}%'";
                                                break;
                                }

                                $where_like .=' or';
                        }
                        $where_like = rtrim($where_like, 'or');
                        $where_like.=") ";
                }
                $where_tag = '';
                if ($tag !== NULL && $tag !== '') {
                        $where_tag = "and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))";
                }

                $where_type = '';
                if ($video_type !== NULL && $video_type !== '') {
                        $where_type = "and asset.nns_video_type='{$video_type}' ";
                }
                $group = 'group by asset.nns_video_id';

                //$sql="select count(asset.*) as sum from nns_assists_item as asset,nns_video_label_bind_content as bind where asset.nns_video_id=bind.nns_video_id  and asset.nns_assist_id='{$asset_id}' and {$child_category} and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))  and {$where_like} and bind.nns_label_id in ({$where_lab})";
                $sql = "select  count(distinct asset.nns_video_id)  as num  from nns_assists_item as asset {$join_tables} where {$asset_sql} {$child_category}  {$where_tag} {$where_type} {$except_sql} {$joins_filds} {$joins_in} {$where_like} ";
                //$r=nl_query_by_db($sql, $dc->db());
                $r = nl_query_by_db($sql, $dc->db());

                if (is_bool($r)) {
                        return $r;
                }
                return $r[0]['num'];
        }

        /**
         * 获取影片相关推荐
         * 目前影片标签类型ID为 1000
         * 思路：
         * 1. 根据影片ID获取影片对应的类型标签组
         * 2. 查询出当前媒资包除当前影片外标签相同的影片列表，
         * 	按符合标签条件个数排序，若媒资包ID为空，则为所有媒资包
         * @param DC
         * @param TAG值
         * @param 视频ID
         * @param 视频类型
         * @param 查询起始
         * @param 查询数量
         * @param 媒资包ID
         * @return true 数据为空
         * 			false 查询出错
         * 			ARRAY 数据集
         */
        static public function search_recom_list_by_video($dc, $tag, $video_id, $video_type, $since = 0, $num = 10, $media_asset_id = NULL, $category_id = NULL) {

                $num = empty($num) ? 10 : $num;

                $video_type = (int) $video_type;
                /* step1 获取影片类型标签集 */
                $label_sql = 'select nns_label_id from nns_video_label_bind_1000 where nns_video_id=\'' . $video_id . '\' and nns_video_type=\'' . $video_type . '\'';

                $r = nl_query_by_db($label_sql, $dc->db());
//    	var_dump($label_sql);die;
                if (!is_array($r))
                        return $r;

                /* step2 获取影片类型相关推荐 */

                $labels = '';
                foreach ($r as $item) {
                        $labels .="'" . $item['nns_label_id'] . "',";
                }
                $labels = rtrim($labels, ',');

                if ($media_asset_id) {
                        $media_asset_query = "and asset.nns_assist_id='{$media_asset_id}'";
                }

                if ($category_id) {
                        $category_query = "and asset.nns_category_id='{$category_id}'";
                }


                $where_tag = '';
                if ($tag === NULL || $tag === '') {
                        $where_tag = "and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))";
                }

                $limit = "limit {$since},{$num}";

                $sql = "select count(DISTINCT label.nns_label_id) as num ,asset.* from  nns_video_label_bind_1000 as label left join nns_assists_item as asset " .
                        "on asset.nns_video_id=label.nns_video_id where label.nns_label_id in ({$labels}) and asset.nns_check=1 " .
                        "{$media_asset_query} {$category_query} {$where_tag} and asset.nns_video_id!='{$video_id}'" .
                        "group by label.nns_video_id order by num desc {$limit}";

                $r = nl_query_by_db($sql, $dc->db());
//    	var_dump($sql);die;
                return $r;
        }

        static public function count_recom_list_by_video($dc, $tag, $video_id, $video_type, $media_asset_id = NULL, $category_id = NULL) {

                $video_type = (int) $video_type;
                /* step1 获取影片类型标签集 */
                $label_sql = 'select nns_label_id from nns_video_label_bind_1000 where nns_video_id=\'' . $video_id . '\' and nns_video_type=\'' . $video_type . '\'';

                $r = nl_query_by_db($label_sql, $dc->db());

                if (!is_array($r))
                        return $r;

                /* step2 获取影片类型相关推荐 */

                $labels = '';
                foreach ($r as $item) {
                        $labels .="'" . $item['nns_label_id'] . "',";
                }
                $labels = rtrim($labels, ',');

                if ($media_asset_id) {
                        $media_asset_query = "and asset.nns_assist_id='{$media_asset_id}'";
                }

                if ($category_id) {
                        $category_query = "and asset.nns_category_id='{$category_id}'";
                }


                $where_tag = '';
                if ($tag === NULL || $tag === '') {
                        $where_tag = "and ((LOCATE(',{$tag},',CONCAT(',',asset.nns_tag)))>0 or asset.nns_tag='' or ISNULL(asset.nns_tag))";
                }


                $sql = "select count(DISTINCT label.nns_video_id) as num  from  nns_video_label_bind_1000 as label left join nns_assists_item as asset " .
                        "on asset.nns_video_id=label.nns_video_id where label.nns_label_id in ({$labels}) and asset.nns_check=1 and asset.nns_video_id!='{$video_id}'" .
                        "{$media_asset_query} {$category_query} {$where_tag} ";
//    			"group by label.nns_video_id ";

                $r = nl_query_by_db($sql, $dc->db());

//    	if (!is_array($r)) return $r;

                return $r[0]['num'];
        }

//	/**
//	 * @param array $labels:标签一维数组
//	 */
//	static private function get_video_id_by_labels($dc,$labels=array(),$policy=NL_DC_AUTO){
//		if($dc->is_cache_enabled()){
//			switch($policy){
//				case NL_DC_AUTO: $result=self::get_video_id_by_labels_cache($dc,$labels);break;
//				case NL_DC_DB: $result=self::get_video_id_by_labels_db($dc,$labels);break;
//				case NL_DC_CACHE: $result=self::get_video_id_by_labels_cache($dc,$labels);break;
//			}
//		}else{
//			$result=self::get_video_id_by_labels_db($dc,$labels);break;
//		}
//		return $result;
//	}
//	static private function get_video_id_by_labels_db($dc,$labels=array()){
//		$label_ids=implode("','", $labels);
//		$label_ids="'".$label_ids."'";
//		$sql="select bind.nns_video_id as video_id from nns_label_type as type,nns_video_label as video,nns_video_label_bind_content as bind where type.nns_name=video.nns_type and video.nns_id=bind.nns_label_id and video.nns_id in ($label_ids) and video.nns_state='1' and video.nns_source='main' and type.nns_type='video' order by type.nns_weight desc";
//		$res=nl_query_by_db($sql, $dc->db());
//		if(!is_bool($res)){//如果获取成功，判断是否打开缓存，如果打开则缓存起来
//			self::set_cache($dc,$sql);
//		}
//		return $res;
//	}
//	static private function get_video_id_by_labels_cache($dc,$labels=array()){
//		$cache=$dc->cache();
//		//先获取key
//		$key=$cache->get("asset_item_bo");
//		//如果获取失败则判断是否需要从数据库读取，如果不是则返回空数组
//		if($key===false){
//			if($policy==NL_DC_AUTO){
//				return self::get_video_id_by_labels_db($dc, $labels);
//			}else{
//				return true;
//			}
//		}
//		$sql="select bind.nns_video_id as video_id from nns_label_type as type,nns_video_label as video,nns_video_label_bind_content as bind where type.nns_name=video.nns_type and video.nns_id=bind.nns_label_id and video.nns_id in ($label_ids) and video.nns_state='1' and video.nns_source='main' and type.nns_type='video' order by type.nns_weight desc";
//		$get_cache=self::get_cache($dc,$sql);
//		return $get_cache;
//	}
//	/**
//	 * 根据sql设置缓存结果集
//	 * @param $dc
//	 * @param string $sql
//	 * @param source $data
//	 * @return bool
//	 */
//	static private function set_cache($dc,$sql,$data){
//		$cache=$dc->cache();
//		if(!$dc->is_cache_enabled()||empty($data)){
//			return false;
//		}
//		//获取key，失败则创建新的key
//		$key=$cache->get("asset_item_bo");
//		if(empty($key)){
//			$key=time();
//			$cache->set("asset_item_bo",$key,self::$cache_time);
//		}
//		$sql=md5($sql);
//		return $cache->set("asset_item_bo|#".$key.$sql,$data,self::$cache_time);
//	}
//	/**
//	 * 根据sql获取缓存结果集
//	 * @param $dc
//	 * @param string $sql
//	 * @return bool
//	 */
//	static private function get_cache($dc,$sql=null){
//		$cache=$dc->cache();
//		if(!$dc->is_cache_enabled()){
//			return false;
//		}
//		$sql=md5($sql);
//		$key=$cache->get("asset_item_bo");
//		return $cache->get("asset_item_bo|#".$key.$sql);
//	}
//	/**
//	 * 删除综合业务缓存key,提供给外部调用
//	 * @param $dc
//	 */
//	static public function del_cache($dc){
//		if(!$dc->is_cache_enabled()){
//			return false;
//		}
//		$cache=$dc->cache();
//		$cache->del("asset_item_bo");
//	}
}

//$dc=nl_get_dc(array("db_policy"=>NL_DB_WRITE,"cache_policy"=>NP_KV_CACHE_TYPE_MEMCACHE));
//$dc->open();
//////$r=nl_asset_item_bo::epg_get_asset_item_by_labels_category($dc, "26", "movie", 1000001, array("00000001","00000002"), 0, 12, 0, NL_ORDER_CLICK_ASC, NL_DC_DB);
//$r=nl_asset_item_bo::epg_get_asset_item_by_labels($dc, "26", array("200000000002","100000000002","100000000001"), 0, 12, 0, NL_ORDER_CLICK_ASC, NL_DC_AUTO);
////$r=nl_asset_item_bo::count_asset_by_labels($dc, "26", "movie", 1000001,  array("00000001","00000002"));
//echo "<pre>";
//var_dump($r);